package de.bastian.hinterleitner.database;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.drawable.Drawable;
import de.bastian.hinterleitner.tools.Category;
import de.bastian.hinterleitner.tools.Entry;
import de.bastian.hinterleitner.tools.EntrySorter;
import de.bastian.hinterleitner.tools.Unit;
import de.hijacksoft.wyc.R;

public class OfflineHelper extends DataHelper{
	
	private SQLiteDatabase db;

	public OfflineHelper(Context context) {
		super(context);
		OpenHelper openHelper = new OpenHelper(context);
		db = openHelper.getWritableDatabase();
	}

	@Override
	public void onResume() {
		if (!db.isOpen()) {
			OpenHelper openHelper = new OpenHelper(context);
			db = openHelper.getWritableDatabase();
		}
	}

	@Override
	public boolean close() {
		entries = null;
		if (db.isOpen()) {
			db.close();
			OpenHelper openHelper = new OpenHelper(context);
			openHelper.getWritableDatabase().close();
			return true;
		} else {
			OpenHelper openHelper = new OpenHelper(context);
			openHelper.getWritableDatabase().close();
			return false;
		}
	}

	@Override
	public void insert(Entry eintrag) {

		if (getEntryById(eintrag.getId()).getId() != -1337) {
			update(eintrag);
		} else {

			if (eintrag.getUnit() == null) {
				String currency = context.getString(R.string.currency);
				eintrag.setUnit(Unit.get(currency, context));
			}

			db.execSQL("INSERT INTO " + TABLE_NAME + " (" + TAG_NAME + ", "
					+ TAG_VALUE + ", " + TAG_YEAR + ", " + TAG_MONTH + ", "
					+ TAG_DAY + ", " + TAG_CATEGORY + ", " + TAG_REFERENCE
					+ ", " + TAG_CURRENCY + ") " + "VALUES (\""
					+ eintrag.getName() + "\", " + eintrag.getValue() + ","
					+ eintrag.getYear() + "," + eintrag.getMonth() + ","
					+ eintrag.getDay() + ",\"" + eintrag.getCategory() + "\", "
					+ eintrag.getRepeatableReference() + ", \""
					+ eintrag.getUnit().getSign() + "\" )");

			if (entries != null) {
				ArrayList<Entry> daysEntries = getFromDBAllEntriesAt(
						eintrag.getYear(), eintrag.getMonth(), eintrag.getDay());
				ArrayList<Entry> possibleEntries = new ArrayList<Entry>();
				for (int i=0; i<daysEntries.size(); i++) {
					Entry e = daysEntries.get(i);
					if (e.getName().equals(eintrag.getName())
							&& e.getCategory().equals(eintrag.getCategory())
							&& e.getValue() == eintrag.getValue()) {
						possibleEntries.add(e);
					}
				}
				if (possibleEntries.size() > 0)
					entries.add(possibleEntries.get(possibleEntries.size() - 1));
			}
		}
	}

	@Override
	public void update(Entry eintrag) {

		if (eintrag.getUnit() == null) {
			String currency = context.getString(R.string.currency);
			eintrag.setUnit(Unit.get(currency, context));
		}

		db.execSQL("UPDATE " + TABLE_NAME + " SET " + TAG_NAME + " = \""
				+ eintrag.getName() + "\" WHERE " + TAG_ID + " = "
				+ eintrag.getId());
		db.execSQL("UPDATE " + TABLE_NAME + " SET " + TAG_VALUE + " = "
				+ eintrag.getValue() + " WHERE " + TAG_ID + " = "
				+ eintrag.getId());
		db.execSQL("UPDATE " + TABLE_NAME + " SET " + TAG_YEAR + " = "
				+ eintrag.getYear() + " WHERE " + TAG_ID + " = "
				+ eintrag.getId());
		db.execSQL("UPDATE " + TABLE_NAME + " SET " + TAG_MONTH + " = "
				+ eintrag.getMonth() + " WHERE " + TAG_ID + " = "
				+ eintrag.getId());
		db.execSQL("UPDATE " + TABLE_NAME + " SET " + TAG_DAY + " = "
				+ eintrag.getDay() + " WHERE " + TAG_ID + " = "
				+ eintrag.getId());
		db.execSQL("UPDATE " + TABLE_NAME + " SET " + TAG_CATEGORY + " = \""
				+ eintrag.getCategory() + "\" WHERE " + TAG_ID + " = "
				+ eintrag.getId());
		db.execSQL("UPDATE " + TABLE_NAME + " SET " + TAG_REFERENCE + " = "
				+ eintrag.getRepeatableReference() + " WHERE " + TAG_ID + " = "
				+ eintrag.getId());
		db.execSQL("UPDATE " + TABLE_NAME + " SET " + TAG_CURRENCY + " = \""
				+ eintrag.getUnit().getSign() + "\" WHERE " + TAG_ID + " = "
				+ eintrag.getId());
		Entry e = getById(eintrag.getId());
		if (e != null) {
			e.setName(eintrag.getName());
			e.setValue(eintrag.getValue());
			e.setCategory(eintrag.getCategory());
			e.setRepeatableReference(eintrag.getRepeatableReference());
			e.setDate(eintrag.getYear(), eintrag.getMonth(), eintrag.getDay());
			e.setUnit(eintrag.getUnit());
		}
	}
	
	@Override
	public void deleteAll() {
		entries = null;
		db.delete(TABLE_NAME, null, null);
		db.delete(TABLE_CATEGORY, null, null);
	}
	
	@Override
	public void delete(int id) {
		if (entries != null) {
			for (int i=0; i<entries.size(); i++) {
				Entry e = entries.get(i);
				if (e.getId() == id) {
					entries.remove(e);
					break;
				}
			}
		}
		db.delete(TABLE_NAME, TAG_ID + "=\"" + id + "\"", null);
	}
	
	@Override
	public Entry getEntryById(int id) {
		if (entries != null) {
			for (int i=0; i<entries.size(); i++) {
				Entry e = entries.get(i);
				if (e.getId() == id)
					return e;
			}
		}
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + TAG_ID
				+ " = \"" + id + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			Entry entry = getEntriesFromCursor(cursor).get(0);
			cursor.close();
			return entry;
		} else {
			cursor.close();
			return new Entry("FAIL", 1337, -1337);
		}
	}
	
	@Override
	public ArrayList<Entry> getAllEntries() {
		if (entries != null) {
			return entries;
		}
		ArrayList<Entry> list = new ArrayList<Entry>();
		String query = "SELECT * FROM " + TABLE_NAME;
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			list = getEntriesFromCursor(cursor);
		}
		cursor.close();
		entries = list;
		return list;
	}
	
	@Override
	public int getNumEntries() {
		if (entries != null)
			return entries.size();
		Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME, null);
		if (!cursor.moveToFirst())
			return 0;
		int result = cursor.getInt(0);
		cursor.close();
		return result;
	}
	
	@Override
	public ArrayList<Entry> getAllEntriesAt(int year, int month, int day) {
		ArrayList<Entry> list = new ArrayList<Entry>();
		if (entries != null) {
			for (int i=0; i<entries.size(); i++) {
				Entry e = entries.get(i);
				if (e.getYear() == year && e.getMonth() == month
						&& e.getDay() == day)
					list.add(e);
			}
			return list;
		}
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + TAG_YEAR
				+ " = \"" + year + "\" AND " + TAG_MONTH + " = \"" + month
				+ "\" AND " + TAG_DAY + " = \"" + day + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			list = getEntriesFromCursor(cursor);
		}
		cursor.close();
		return list;
	}
	
	@Override
	protected ArrayList<Entry> getFromDBAllEntriesAt(int year, int month,
			int day) {
		ArrayList<Entry> list = new ArrayList<Entry>();
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + TAG_YEAR
				+ " = \"" + year + "\" AND " + TAG_MONTH + " = \"" + month
				+ "\" AND " + TAG_DAY + " = \"" + day + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			list = getEntriesFromCursor(cursor);
		}
		cursor.close();
		return list;
	}
	
	@Override
	public ArrayList<Entry> getAllEntriesContaining(String substring) {
		ArrayList<Entry> list = new ArrayList<Entry>();
		if (entries != null) {
			for (int i=0; i<entries.size(); i++) {
				Entry e = entries.get(i);
				if (e.getName().toUpperCase().contains(substring.toUpperCase()))
					list.add(e);
			}
			return list;
		}
		if (substring.replaceAll(" ", "").equals("") || substring.length() < 2)
			return list;
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE upper("
				+ TAG_NAME + ") like \"%" + substring.toUpperCase() + "%\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			list = getEntriesFromCursor(cursor);
		}
		cursor.close();
		return list;
	}
	
	@Override
	public ArrayList<Entry> getAllEntriesAt(int year, int month) {
		ArrayList<Entry> list = new ArrayList<Entry>();
		if (entries != null) {
			for (int i=0; i<entries.size(); i++) {
				Entry e = entries.get(i);
				if (e.getYear() == year && e.getMonth() == month)
					list.add(e);
			}
			return list;
		}
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + TAG_YEAR
				+ " = \"" + year + "\" AND " + TAG_MONTH + " = \"" + month
				+ "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			list = getEntriesFromCursor(cursor);
		}
		cursor.close();
		return list;
	}

	@Override
	public ArrayList<Entry> getAllEntriesAt(int year) {
		ArrayList<Entry> list = new ArrayList<Entry>();
		if (entries != null) {
			for (int i=0; i<entries.size(); i++) {
				Entry e = entries.get(i);
				if (e.getYear() == year)
					list.add(e);
			}
			return list;
		}
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + TAG_YEAR
				+ " = \"" + year + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			list = getEntriesFromCursor(cursor);
		}
		cursor.close();
		return list;
	}
	
	@Override
	public ArrayList<Entry> getAllEntriesByCategory(Category c) {
		ArrayList<Entry> list = new ArrayList<Entry>();
		if (entries != null) {
			for (int i=0; i<entries.size(); i++) {
				Entry e = entries.get(i);
				if (e.getCategory().equals(new Category(c.name, c.icon, c.id)))
					list.add(e);
			}
			return list;
		}
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + TAG_CATEGORY
				+ " = \"" + c.name + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			list = getEntriesFromCursor(cursor);
		}
		cursor.close();
		return list;
	}
	
	@Override
	public boolean renameCategory(Category c, String newName) {
		if (!getAllCategories().contains(new Category(newName, icon, -1))
				&& !c.name.equals(context.getString(R.string.uncategorized))) {
			ArrayList<Entry> items = getAllEntriesByCategory(c);
			for (int i=0; i<items.size(); i++) {
				Entry e = items.get(i);
				e.getCategory().name = newName;
				update(e);
			}
			if (c.id == -1) {
				db.execSQL("UPDATE " + TABLE_CATEGORY + " SET " + TAG_NAME
						+ " = \"" + newName + "\" WHERE " + TAG_NAME + " = \""
						+ c.name + "\"");
			} else {
				db.execSQL("UPDATE " + TABLE_CATEGORY + " SET " + TAG_NAME
						+ " = \"" + newName + "\" WHERE " + TAG_ID + " = \""
						+ c.id + "\"");
			}
			if (entries != null) {
				for (int i=0; i<entries.size(); i++) {
					Entry e = entries.get(i);
					if (e.getCategory().equals(c))
						e.getCategory().name = newName;
				}
			}
			c.name = newName;
			return true;
		} else
			return false;
	}
	
	@Override
	public boolean deleteCategory(Category category) {
		if (!category.equals(context.getString(R.string.uncategorized))
				&& category.id != -1) {
			ArrayList<Entry> items = getAllEntriesByCategory(category);
			for (int i=0; i<items.size(); i++) {
				Entry e = items.get(i);
				e.setCategory(new Category(context
						.getString(R.string.uncategorized), icon, -1));
				update(e);
			}
			db.delete(TABLE_CATEGORY, TAG_ID + "=\"" + category.id + "\"", null);
			addCategory(new Category(context.getString(R.string.uncategorized),
					icon, -1));
			if (entries != null) {
				for (int i=0; i<entries.size(); i++) {
					Entry e = entries.get(i);
					if (e.getCategory().equals(category)) {
						e.setCategory(new Category(context
								.getString(R.string.uncategorized), icon, -1));
					}
				}
			}
			return true;
		} else
			return false;
	}
	
	@Override
	public ArrayList<Category> getAllCategories() {
		if (update) {
			String query = "SELECT DISTINCT " + TAG_CATEGORY + " FROM "
					+ TABLE_NAME;
			Cursor cursor = db.rawQuery(query, null);
			if (cursor.moveToFirst()) {
				do {
					Category c = new Category(cursor.getString(0), icon, -1);
					addCategory(c);
				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		ArrayList<Category> list = new ArrayList<Category>();
		String query = "SELECT DISTINCT " + TAG_NAME + ", " + TAG_DRAWABLE
				+ ", " + TAG_ID + " FROM " + TABLE_CATEGORY;
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				list.add(new Category(cursor.getString(0), cursor.getString(1),
						Integer.parseInt(cursor.getString(2))));
			} while (cursor.moveToNext());
		}
		cursor.close();
		Collections.sort(list);
		return list;
	}
	
	@Override
	public ArrayList<String> getAllEntryNames() {
		ArrayList<String> list = new ArrayList<String>();
		if (entries != null) {
			for (int i = 0; i < entries.size(); i++) {
				if (!list.contains(entries.get(i).getName()))
					list.add(entries.get(i).getName());
			}
		} else {
			String query = "SELECT DISTINCT " + TAG_NAME + " FROM "
					+ TABLE_NAME;
			Cursor cursor = db.rawQuery(query, null);
			if (cursor.moveToFirst()) {
				do {
					list.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		return list;
	}
	
	@Override
	public ArrayList<Integer> getAllYears() {
		ArrayList<Integer> list = new ArrayList<Integer>();
		String query = "SELECT DISTINCT " + TAG_YEAR + " FROM " + TABLE_NAME;
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getInt(0));
			} while (cursor.moveToNext());
		}
		cursor.close();
		Collections.sort(list);
		return list;
	}
	
	@Override
	public ArrayList<Integer> getAllMonths(int year) {
		ArrayList<Integer> list = new ArrayList<Integer>();
		String query = "SELECT DISTINCT " + TAG_MONTH + " FROM " + TABLE_NAME
				+ " WHERE " + TAG_YEAR + "=\"" + year + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getInt(0));
			} while (cursor.moveToNext());
		}
		cursor.close();
		Collections.sort(list);
		return list;
	}
	
	@Override
	public ArrayList<Integer> getAllDays(int year, int month) {
		ArrayList<Integer> list = new ArrayList<Integer>();
		String query = "SELECT DISTINCT " + TAG_DAY + " FROM " + TABLE_NAME
				+ " WHERE " + TAG_YEAR + " = \"" + year + "\" AND " + TAG_MONTH
				+ " = \"" + month + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getInt(0));
			} while (cursor.moveToNext());
		}
		cursor.close();
		Collections.sort(list);
		return list;
	}
	
	@Override
	public Drawable getCategoryDrawable(String category) {
		String name = icon;
		String query = "SELECT " + TAG_DRAWABLE + " FROM " + TABLE_CATEGORY
				+ " WHERE " + TAG_NAME + " = \"" + category + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			name = cursor.getString(0);
		} else {
			name = category;
		}
		int id = context.getResources().getIdentifier(name, "drawable",
				context.getPackageName());
		if (id == 0) {
			id = context.getResources().getIdentifier(icon, "drawable",
					context.getPackageName());
		}
		Drawable drawable = context.getResources().getDrawable(id);
		cursor.close();
		return drawable;
	}
	
	@Override
	public String getCategoryDrawableName(String category) {
		String name = icon;
		String query = "SELECT " + TAG_DRAWABLE + " FROM " + TABLE_CATEGORY
				+ " WHERE " + TAG_NAME + " = \"" + category + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			name = cursor.getString(0);
		} else {
			name = category;
		}
		cursor.close();
		return name;
	}
	
	@Override
	public int getCategoryID(String category) {
		int id = -1;
		String query = "SELECT " + TAG_ID + " FROM " + TABLE_CATEGORY
				+ " WHERE " + TAG_NAME + " = \"" + category + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			id = cursor.getInt(0);
		}
		cursor.close();
		return id;
	}
	
	@Override
	public void setCategoryDrawable(String cat, String drawable) {
		db.execSQL("UPDATE " + TABLE_CATEGORY + " SET " + TAG_DRAWABLE
				+ " = \"" + drawable + "\" WHERE " + TAG_NAME + " = \"" + cat
				+ "\"");	
	}
	
	@Override
	public boolean addCategory(Category cat) {
		if (!getAllCategories().contains(cat)) {
			db.execSQL("INSERT INTO " + TABLE_CATEGORY + " (" + TAG_NAME + ", "
					+ TAG_DRAWABLE + ") " + "VALUES (\"" + cat.name + "\", \""
					+ cat.icon + "\")");
			return true;
		} else
			return false;
	}
	
	@Override
	protected void addTrashCategory() {
		db.execSQL("INSERT INTO " + TABLE_CATEGORY + " (" + TAG_ID + ","
				+ TAG_NAME + ", " + TAG_DRAWABLE + ") " + "VALUES (-1 , \""
				+ context.getString(R.string.uncategorized) + "\", \"" + icon
				+ "\")");
	}
	
	@Override
	protected ArrayList<Entry> getAllRepeatables(int repeatableReference,
			Date date) {
		ArrayList<Entry> list = new ArrayList<Entry>();
		if (entries != null) {
			for (int i=0; i<entries.size(); i++) {
				Entry e = entries.get(i);
				if (e.getRepeatableReference() == repeatableReference) {
					if (e.getDate().getTime() >= date.getTime()) {
						list.add(e);
					}
				}
			}
			return list;
		}
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE "
				+ TAG_REFERENCE + " = " + repeatableReference;
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			list = getEntriesFromCursor(cursor);
		}
		for (int i = 0; i < list.size(); i++) {
			if (list.get(i).getDate().getTime() < date.getTime()) {
				list.remove(i--);
			}
		}
		cursor.close();
		list = EntrySorter.getSingleton(context).sortBy(list,
				EntrySorter.SORT_BY_DATE);
		return list;
	}
	
	@Override
	protected int getNewRefID() {
		String query = "SELECT MAX(" + TAG_REFERENCE + ") FROM " + TABLE_NAME;
		Cursor c = db.rawQuery(query, null);
		int result = -1;
		if (c.moveToFirst()) {
			result = c.getInt(0);
		}
		return result + 1;
	}
	
	@Override
	public ArrayList<Entry> getAllEntriesGroupedByName() {
		ArrayList<Entry> list = new ArrayList<Entry>();
		if (entries != null) {
			for (int i = 0; i < entries.size(); i++) {
				if (!containsEntriesWith(entries.get(i).getName(), list)) {
					list.add(entries.get(i));
				}
			}
		} else {
			String query = "SELECT * FROM " + TABLE_NAME + " GROUP BY "
					+ TAG_NAME;
			Cursor cursor = db.rawQuery(query, null);
			list = getEntriesFromCursor(cursor);
		}
		return list;
	}
	
	@Override
	public Category getCategoryByName(String name) {
		String query = "SELECT " + TAG_NAME + ", " + TAG_DRAWABLE + ", "
				+ TAG_ID + " FROM " + TABLE_CATEGORY + " WHERE " + TAG_NAME
				+ " = \"" + name + "\"";
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			String cat = cursor.getString(0);
			String icon = cursor.getString(1);
			int id = cursor.getInt(2);
			cursor.close();
			return new Category(cat, icon, id);
		} else {
			cursor.close();
			return new Category(context.getString(R.string.uncategorized),
					icon, -1);
		}
	}
	
}
